package com.javaDemo.excel;

import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.poi.ss.usermodel.DataValidation;
import org.apache.poi.ss.usermodel.DataValidationConstraint;
import org.apache.poi.ss.usermodel.DataValidationHelper;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddressList;

public class DataValidationHandler implements SheetWriteHandler {

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        DataValidationHelper helper = sheet.getDataValidationHelper();

        // 第五列（索引4）数据校验：是、否
        CellRangeAddressList col5Range = new CellRangeAddressList(1, 65535, 5, 5);
        DataValidationConstraint col5Constraint = helper.createExplicitListConstraint(new String[]{"是", "否"});
        DataValidation col5Validation = helper.createValidation(col5Constraint, col5Range);
        col5Validation.setEmptyCellAllowed(true);
        col5Validation.setSuppressDropDownArrow(true);
        sheet.addValidationData(col5Validation);

        // 第七列（索引6）数据校验：Y、R、G
        CellRangeAddressList col7Range = new CellRangeAddressList(1, 65535, 7, 7);
        DataValidationConstraint col7Constraint = helper.createExplicitListConstraint(new String[]{"Y", "R", "G"});
        DataValidation col7Validation = helper.createValidation(col7Constraint, col7Range);
        col7Validation.setEmptyCellAllowed(true);
        col7Validation.setSuppressDropDownArrow(true);
        sheet.addValidationData(col7Validation);
    }
}